package fu.abc.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import fu.abc.model.Category;

public class CategoryDAO {
	
	
	Connection connect = null;
	PreparedStatement pre = null;
	ResultSet result = null;
	Statement state = null;
	
	
	// add new category
		public boolean addCategory (Category cate) throws SQLException {
			
//			connect to DB
			connect = UtilConnection.getConnection();
			
			if(connect != null){
					try {		
					String querry = "INSERT INTO CATEGORY VALUES(?)";
//					execute query
					pre = connect.prepareStatement(querry);	
					pre.setString(1, cate.getCategory());				
					pre.execute();
					
					return true;
				} catch (SQLException e) {
					e.printStackTrace();
					return false;
				}finally{
					if(connect!=null){
//						close DB
						connect.close();
					}
				}
			}else 
				return false;
		}
		
		// list category by searching
		
		public List<Category> listCategory (String category) throws SQLException{
			
			List<Category> list = new ArrayList<>();
			
			String query ="";
			try {
//				connect to DB
				connect = UtilConnection.getConnection();
				
				if(connect!=null){
					state = connect.createStatement();
//					select * from category
					if(("".equals(category) || category==null)){
						query = "SELECT * FROM CATEGORY";
					}else{
//						select by text
						query = "SELECT * FROM CATEGORY WHERE FREETEXT(category,'"+category+"')";
					}
//					execute query
					result = state.executeQuery(query);
					while(result.next()){
						Category cate = new Category();
						cate.setIdcate(result.getInt("idcate"));
						cate.setCategory(result.getString("category"));
						list.add(cate);
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				if(connect!=null){
					connect.close();
				}
			}
			return list; 
		}
		
		// edit category
		
		public void editCategory(Category cate){
			
			String query = "UPDATE CATEGORY SET category=? WHERE idcate=?";
			try {
//				connect to DB
				connect = UtilConnection.getConnection();
				if(connect!=null){
					pre = connect.prepareStatement(query);
					
					pre.setString(1,cate.getCategory());
					pre.setInt(2, cate.getIdcate());
					pre.execute();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally{
				if(connect!=null){
					try {
						connect.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
		
		// get category
		public Category getCategory (int id) throws SQLException{
			
			Category cate = new Category();
			
			String query = "SELECT * FROM CATEGORY where idcate='"+id+"'";
			try {
//				connect to DB
				connect = UtilConnection.getConnection();
				
				if(connect!=null){
					state = connect.createStatement();
//					execute query
					result = state.executeQuery(query);
					while(result.next()){
						
						cate.setIdcate(result.getInt("idcate"));
						cate.setCategory(result.getString("category"));
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				if(connect!=null){
					connect.close();
				}
			}
			return cate; 
		}
		
		// list category
		
		public List<Category> listAllCategory () throws SQLException{
			
			List<Category> list = new ArrayList<>();
			
			String query = "SELECT * FROM CATEGORY";
			try {
//				connect to DB
				connect = UtilConnection.getConnection();
				
				if(connect!=null){
					state = connect.createStatement();
//					execute query
					result = state.executeQuery(query);
					while(result.next()){
						Category cate = new Category();
						cate.setIdcate(result.getInt("idcate"));
						cate.setCategory(result.getString("category"));
						list.add(cate);
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				if(connect!=null){
					connect.close();
				}
			}
			return list; 
		}
//		
		
}
